package sk.stuba.fiit.foo07.genex.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import sk.stuba.fiit.foo07.genex.beans.Category;
import sk.stuba.fiit.foo07.genex.beans.Question;
import sk.stuba.fiit.foo07.genex.exceptions.QuestionInCategoryException;
import sk.stuba.fiit.foo07.genex.exceptions.TestInTestCategoryException;

public class CategoryDaoDerby extends BasicDao implements CategoryDao {

    protected ResultSet rs;
    protected Category c;
    protected PreparedStatement pstmt;
    protected ArrayList<Category> a_list;
    protected String sql;
    protected boolean flag;

    private static final String sqlUpdateTest = "UPDATE TestCategory SET Name=?,Description=?,LastUpdate=CURRENT_TIMESTAMP,userID=?,parentCategoryID=? WHERE CategoryID=?";
    private static final String sqlUpdate = "UPDATE Category SET Name=?,Description=?,LastUpdate=CURRENT_TIMESTAMP,userID=?,parentCategoryID=? WHERE CategoryID=?";
    private static final String sqlGetTest = "SELECT * FROM TestCategory WHERE categoryID=?";
    private static final String sqlGetAllRootTest = "SELECT * FROM TestCategory WHERE parentCategoryID is NULL";
    private static final String sqlGetAllTestByParent = "SELECT * FROM TestCategory WHERE parentCategoryID=?";
    private static final String sqlHasTestSubcat = "SELECT * FROM TestCategory WHERE parentCategoryID=?";
    private static final String sqlHasSubcat = "SELECT * FROM Category WHERE parentCategoryID=?";
    private static final String sqlGet = "SELECT * FROM Category WHERE categoryID=?";
    private static final String sqlGetCategoryIDByQuestionID = "SELECT * FROM CategoryQuestion WHERE questionID=?";
    private static final String sqlGetCategoryName = "SELECT * FROM Category WHERE name =?";
    private static final String sqlGetAllByParent = "SELECT * FROM Category WHERE parentCategoryID=?";
    private static final String sqlGetAllRoot = "SELECT * FROM Category WHERE parentCategoryID is NULL";
    private static final String sqlDeleteTest = "DELETE FROM TestCategory WHERE categoryID=?";
    private static final String sqlDelete = "DELETE FROM Category WHERE categoryID=?";
    private static final String sqlTestRoot = "INSERT INTO TESTCATEGORY VALUES (?,?, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP,DEFAULT, NULL, ? )";
    private static final String sqlTest = "INSERT INTO TESTCATEGORY VALUES (?,?, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP,DEFAULT, ?, ? )";
    private static final String sqlAddTestAll = "INSERT INTO TESTCATEGORY VALUES (?,?, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP,DEFAULT, ?, ? )";
    private static final String sqlAddRoot = "INSERT INTO CATEGORY VALUES (?,?, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP,DEFAULT, NULL, ? )";
    private static final String sqlAdd = "INSERT INTO CATEGORY VALUES (?,?, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP,DEFAULT, ?, ? )";
    private static final String sqlAddAll = "INSERT INTO CATEGORY VALUES (?,?, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP,DEFAULT, ?, ? )";
    private static final String sqlGetQuestionsByCategoryIDCount = "SELECT COUNT(*) FROM CategoryQuestion WHERE CategoryQuestion.categoryID = ?";

    public CategoryDaoDerby(Connection con) {
        super(con);
    }

    @Override
    public void addCategories(Integer parentCategoryID,
            ArrayList<Category> toAdd) throws SQLException {

        int len = toAdd.size();
        for (int i = 0; i < len; i++)
            addCategory(parentCategoryID, toAdd.get(i));

        return;
    }

    @Override
    public void addCategory(Integer parentCategoryID, Category toAdd)
            throws SQLException {
        try {
            if (parentCategoryID != null)
                pstmt = con.prepareStatement(sqlAdd,
                        Statement.RETURN_GENERATED_KEYS);
            else
                pstmt = con.prepareStatement(sqlAddRoot,
                        Statement.RETURN_GENERATED_KEYS);

            pstmt.setString(1, toAdd.getName());
            pstmt.setString(2, toAdd.getDescription());
            if (parentCategoryID != null) {
                pstmt.setInt(3, parentCategoryID);
                pstmt.setInt(4, toAdd.getUserTD());
            } else
                pstmt.setInt(3, toAdd.getUserTD());
            pstmt.execute();

            rs = pstmt.getGeneratedKeys();
            rs.next();
            toAdd.setCategoryID(rs.getInt(1));
            rs.close();

        } catch (SQLException e) {
            pstmt = null;
            sql = null;
            rs = null;
            throw e;
        }
        return;
    }

    @Override
    public void addRootCategory(Category toAdd) throws SQLException {
        addCategory(null, toAdd);

    }

    @Override
    public void addTestCategories(Integer parentTestCategoryID,
            ArrayList<Category> toAdd) throws SQLException {

        int len = toAdd.size();
        for (int i = 0; i < len; i++)
            addTestCategory(parentTestCategoryID, toAdd.get(i));

    }

    @Override
    public void addTestCategory(Integer parentTestCategoryID, Category toAdd)
            throws SQLException {
        try {
            if (parentTestCategoryID != null)
                pstmt = con.prepareStatement(sqlTest,
                        Statement.RETURN_GENERATED_KEYS);
            else
                pstmt = con.prepareStatement(sqlTestRoot,
                        Statement.RETURN_GENERATED_KEYS);

            pstmt.setString(1, toAdd.getName());
            pstmt.setString(2, toAdd.getDescription());
            if (parentTestCategoryID != null) {
                pstmt.setInt(3, parentTestCategoryID);
                pstmt.setInt(4, toAdd.getUserTD());
            } else
                pstmt.setInt(3, toAdd.getUserTD());
            pstmt.execute();
            rs = pstmt.getGeneratedKeys();
            rs.next();
            toAdd.setCategoryID(rs.getInt(1));
            rs.close();

        } catch (SQLException e) {
            pstmt = null;
            sql = null;
            rs = null;
            throw e;
        }
    }

    @Override
    public void addRootTestCategory(Category toAdd) throws SQLException {
        addTestCategory(null, toAdd); // calls similar method but without parentId

    }

    @Override
    public void deleteCategories(ArrayList<Category> toDelete)
            throws QuestionInCategoryException, SQLException {

        int len = toDelete.size();
        for (int i = 0; i < len; i++) {
            deleteCategory(toDelete.get(i));

        }

    }

    @Override
    public void deleteCategory(Category toDelete)
            throws QuestionInCategoryException, SQLException {
        try {

            pstmt = con.prepareStatement(sqlDelete);
            pstmt.setInt(1, toDelete.getCategoryID());
            pstmt.execute();

        } catch (SQLException e) {

            pstmt = null;
            sql = null;
            if ("23503".equals(e.getSQLState()))
                throw new QuestionInCategoryException(e);
            else
                throw e;
        }

    }

    @Override
    public void deleteTestCategories(ArrayList<Category> toDelete)
            throws TestInTestCategoryException, SQLException {

        int len = toDelete.size();
        for (int i = 0; i < len; i++) {
            deleteTestCategory(toDelete.get(i));
        }

    }

    @Override
    public void deleteTestCategory(Category toDelete)
            throws TestInTestCategoryException, SQLException {

        try {

            pstmt = con.prepareStatement(sqlDeleteTest);
            pstmt.setInt(1, toDelete.getCategoryID());
            pstmt.execute();

        } catch (SQLException e) {
            pstmt = null;
            sql = null;
            if ("23503".equals(e.getSQLState()))
                throw new TestInTestCategoryException(e);
            else
                throw e;
        }

    }

    @Override
    public ArrayList<Category> getRootCategories() throws SQLException {
        try {

            pstmt = con.prepareStatement(sqlGetAllRoot,
                    ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_UPDATABLE);
            rs = pstmt.executeQuery();
            rs.last();
            a_list = new ArrayList<Category>(rs.getRow());
            rs.beforeFirst();

            while (rs.next()) {
                c = new Category();
                c.setName(rs.getString("Name"));
                c.setDescription(rs.getString("Description"));
                c.setCreated(rs.getTimestamp("Created"));
                c.setLastUpdate(rs.getTimestamp("LastUpdate"));
                c.setCategoryID(rs.getInt("categoryID"));
                c.setParentCategoryId(null);
                c.setUserTD(rs.getInt("userID"));

                a_list.add(c);
            }
            rs.close();
        } catch (SQLException e) {
            pstmt = null;
            rs = null;
            throw e;
        }
        return a_list;
    }

    @Override
    public ArrayList<Category> getCategoriesByParentID(Integer categoryID)
            throws SQLException {
        try {

            if (categoryID == null)
                pstmt = con.prepareStatement(sqlGetAllRoot,
                        ResultSet.TYPE_SCROLL_INSENSITIVE,
                        ResultSet.CONCUR_UPDATABLE);
            else {
                pstmt = con.prepareStatement(sqlGetAllByParent,
                        ResultSet.TYPE_SCROLL_INSENSITIVE,
                        ResultSet.CONCUR_UPDATABLE);
                pstmt.setInt(1, categoryID);
            }
            rs = pstmt.executeQuery();
            rs.last();
            a_list = new ArrayList<Category>(rs.getRow());
            rs.beforeFirst();

            while (rs.next()) {
                c = new Category();
                c.setName(rs.getString("Name"));
                c.setDescription(rs.getString("Description"));
                c.setCreated(rs.getTimestamp("Created"));
                c.setLastUpdate(rs.getTimestamp("LastUpdate"));
                c.setCategoryID(rs.getInt("categoryID"));
                c.setParentCategoryId(rs.getInt("parentCategoryID"));
                c.setUserTD(rs.getInt("userID"));

                a_list.add(c);
            }
            rs.close();
        } catch (SQLException e) {
            pstmt = null;
            rs = null;
            throw e;
        }
        return a_list;
    }

    @Override
    public Category getCategoryByID(Integer categoryID) throws SQLException {
        try {
            c = new Category();

            pstmt = con.prepareStatement(sqlGet);
            pstmt.setInt(1, categoryID);
            rs = pstmt.executeQuery();

            rs.next();
            c.setName(rs.getString("Name"));
            c.setDescription(rs.getString("Description"));
            c.setCreated(rs.getTimestamp("Created"));
            c.setLastUpdate(rs.getTimestamp("LastUpdate"));
            c.setCategoryID(categoryID);
            c.setParentCategoryId(rs.getInt("parentCategoryID"));
            c.setUserTD(rs.getInt("userID"));
            rs.close();

        } catch (SQLException e) {
            c = null;
            sql = null;
            pstmt = null;
            rs = null;
            throw e;
        }
        return c;
    }

    @Override
    public boolean hasSubcategories(Integer categoryID) throws SQLException {
        try {

            pstmt = con.prepareStatement(sqlHasSubcat);
            pstmt.setInt(1, categoryID);
            rs = pstmt.executeQuery();

            flag = rs.next();

        } catch (SQLException e) {
            sql = null;
            pstmt = null;
            rs = null;
            throw e;
        }
        return flag;
    }

    @Override
    public boolean hasTestSubcategories(Integer categoryID) throws SQLException {
        try {

            pstmt = con.prepareStatement(sqlHasTestSubcat);
            pstmt.setInt(1, categoryID);
            rs = pstmt.executeQuery();

            flag = rs.next();

        } catch (SQLException e) {
            sql = null;
            pstmt = null;
            rs = null;
            throw e;
        }
        return flag;
    }

    @Override
    public ArrayList<Category> getTestCategoriesByParentID(Integer categoryID)
            throws SQLException {
        try {

            if (categoryID == null)
                pstmt = con.prepareStatement(sqlGetAllRootTest,
                        ResultSet.TYPE_SCROLL_INSENSITIVE,
                        ResultSet.CONCUR_UPDATABLE);
            else {
                pstmt = con.prepareStatement(sqlGetAllTestByParent,
                        ResultSet.TYPE_SCROLL_INSENSITIVE,
                        ResultSet.CONCUR_UPDATABLE);
                pstmt.setInt(1, categoryID);
            }
            rs = pstmt.executeQuery();
            rs.last();
            a_list = new ArrayList<Category>(rs.getRow());
            rs.beforeFirst();

            while (rs.next()) {
                c = new Category();
                c.setName(rs.getString("Name"));
                c.setDescription(rs.getString("Description"));
                c.setCreated(rs.getTimestamp("Created"));
                c.setLastUpdate(rs.getTimestamp("LastUpdate"));
                c.setCategoryID(rs.getInt("categoryID"));
                c.setParentCategoryId(rs.getInt("parentCategoryID"));
                c.setUserTD(rs.getInt("userID"));

                a_list.add(c);
            }
            rs.close();
        } catch (SQLException e) {
            pstmt = null;
            rs = null;
            throw e;
        }
        return a_list;
    }

    @Override
    public ArrayList<Category> getRootTestCategories() throws SQLException {
        try {

            pstmt = con.prepareStatement(sqlGetAllRootTest,
                    ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_UPDATABLE);
            rs = pstmt.executeQuery();
            rs.last();
            a_list = new ArrayList<Category>(rs.getRow());
            rs.beforeFirst();

            while (rs.next()) {
                c = new Category();
                c.setName(rs.getString("Name"));
                c.setDescription(rs.getString("Description"));
                c.setCreated(rs.getTimestamp("Created"));
                c.setLastUpdate(rs.getTimestamp("LastUpdate"));
                c.setCategoryID(rs.getInt("categoryID"));
                c.setParentCategoryId(null);
                c.setUserTD(rs.getInt("userID"));

                a_list.add(c);
            }
            rs.close();
        } catch (SQLException e) {
            pstmt = null;
            rs = null;
            throw e;
        }
        return a_list;
    }

    @Override
    public Category getTestCategoryByID(Integer testCategoryID)
            throws SQLException {
        try {
            c = new Category();

            pstmt = con.prepareStatement(sqlGetTest);
            pstmt.setInt(1, testCategoryID);
            rs = pstmt.executeQuery();

            rs.next();
            c.setName(rs.getString("Name"));
            c.setDescription(rs.getString("Description"));
            c.setCreated(rs.getTimestamp("Created"));
            c.setLastUpdate(rs.getTimestamp("LastUpdate"));
            c.setCategoryID(testCategoryID);
            c.setParentCategoryId(rs.getInt("parentCategoryID"));
            c.setUserTD(rs.getInt("userID"));
            rs.close();

        } catch (SQLException e) {
            c = null;
            sql = null;
            pstmt = null;
            rs = null;
            throw e;
        }
        return c;
    }

    @Override
    public void updateCategory(Category toUpdate) throws SQLException {
        try {

            pstmt = con.prepareStatement(sqlUpdate);
            pstmt.setString(1, toUpdate.getName());
            pstmt.setString(2, toUpdate.getDescription());
            pstmt.setInt(3, toUpdate.getUserTD());
            pstmt.setInt(4, toUpdate.getParentCategoryId());
            pstmt.setInt(5, toUpdate.getCategoryID());

            pstmt.execute();

        } catch (SQLException e) {
            sql = null;
            pstmt = null;
            throw e;
        }

    }

    @Override
    public void updateTestCategory(Category toUpdate) throws SQLException {
        try {

            pstmt = con.prepareStatement(sqlUpdateTest);
            pstmt.setString(1, toUpdate.getName());
            pstmt.setString(2, toUpdate.getDescription());
            pstmt.setInt(3, toUpdate.getUserTD());
            pstmt.setInt(4, toUpdate.getParentCategoryId());
            pstmt.setInt(5, toUpdate.getCategoryID());

            pstmt.execute();

        } catch (SQLException e) {
            sql = null;
            pstmt = null;
            throw e;
        }
    }

    @Override
    @Deprecated
    public int getCategoryByQuestionID(Integer questionID) throws SQLException {
        pstmt = con.prepareStatement(sqlGetCategoryIDByQuestionID);
        pstmt.setInt(1, questionID);

        rs = pstmt.executeQuery();
        if (rs.next()) {
            int ret = rs.getInt("categoryID");
            rs.close();
            rs = null;
            return ret;
        }

        return -1;
    }

    @Override
    public ArrayList<Integer> getCategoriesIDByQuestionID(Integer questionID)
            throws SQLException {
        ArrayList<Integer> ret_list = null;
        try {
            pstmt = con.prepareStatement(sqlGetCategoryIDByQuestionID,
                    ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_UPDATABLE);
            pstmt.setInt(1, questionID);
            rs = pstmt.executeQuery();
            rs.last();
            ret_list = new ArrayList<Integer>(rs.getRow());
            rs.beforeFirst();
            while (rs.next()) {
                ret_list.add(rs.getInt("categoryID"));
            }
            rs.close();
            rs = null;
        } catch (SQLException ex) {
            pstmt = null;
            rs = null;
        }
        return ret_list;
    }

    @Override
    public ArrayList<Category> getCategoriesByName(String name)
            throws SQLException {
        pstmt = con.prepareStatement(sqlGetCategoryName,
                ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
        pstmt.setString(1, name);

        rs = pstmt.executeQuery();
        rs.last();
        a_list = new ArrayList<Category>(rs.getRow());
        rs.beforeFirst();

        while (rs.next()) {
            c = new Category();
            c.setName(rs.getString("Name"));
            c.setDescription(rs.getString("Description"));
            c.setCreated(rs.getTimestamp("Created"));
            c.setLastUpdate(rs.getTimestamp("LastUpdate"));
            c.setCategoryID(rs.getInt("categoryID"));
            c.setParentCategoryId(rs.getInt("parentCategoryID"));
            c.setUserTD(rs.getInt("userID"));
            a_list.add(c);
            rs.close();
        }
        return a_list;
    }

    @Override
    public void copyCategory(Category toCopy, Category destination)
            throws SQLException {
        QuestionDao qDao = new QuestionDaoDerby(con);
        ArrayList<Question> questions = qDao.getQuestionsByCategoryID(toCopy
                .getCategoryID());
        ArrayList<Category> childCategories = getCategoriesByParentID(toCopy
                .getCategoryID());

        toCopy.setCategoryID(null);
        addCategory(destination.getCategoryID(), toCopy);

        for (Question q : questions) {
            qDao.copyQuestion(q.getQuestionID(), toCopy.getCategoryID());
        }
        questions = null;

        for (Category cat : childCategories) {
            copyCategory(cat, toCopy);
        }

    }

    @Override
    public Integer copyCategory(Integer toCopyID, Category destination)
            throws SQLException {
        Category c = getCategoryByID(toCopyID);
        copyCategory(c, destination);
        return c.getCategoryID();
    }

    public int questionCount(Integer categoryID) throws SQLException {
        try {

            pstmt = con.prepareStatement(sqlGetQuestionsByCategoryIDCount,
                    ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_UPDATABLE);
            pstmt.setInt(1, categoryID);
            rs = pstmt.executeQuery();
            rs.first();
            int ret = rs.getInt(1);
            rs.close();
            return ret;
        } catch (SQLException e) {
            sql = null;
            pstmt = null;
            rs = null;
            throw e;

        }

    }
}